# -*- coding:utf-8 -*-
"""
@file: db_helper.py
@time: 2019/12/17 18:20
@author: ljt
descript:
"""
import traceback

import pandas as pd
from sqlalchemy.orm import sessionmaker, scoped_session

from ScoreCard.log.logger_config import logger
from ScoreCard.config.db_config import DBConfig



def get_engine(engine_str):
    from sqlalchemy import create_engine
    # engine_str = 'postgresql://postgres:zl@localhost:5432/base_db'
    engine = create_engine(engine_str,
                           max_overflow=2,  # 超过连接池大小外最多创建的连接
                           pool_size=5,  # 连接池大小
                           pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
                           pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                           )  # create_engine说明：dialect[+driver]://user:password@host/dbname[?key=value..]
    # plpy = engine.connect()
    SessionFactory = sessionmaker(bind=engine)
    session = scoped_session(SessionFactory)
    return session,engine

# import psycopg2
# from DBUtils.PooledDB import PooledDB
# class DataBase():
#     # 连接
#     def __init__(self, match):
#         try:
#             self.psycopg_pool = PooledDB(psycopg2, mincached=5, blocking=True, user=ConfParams.USER,
#                                          password=ConfParams.PASSWD, database=match, host=ConfParams.HOST,
#                                          port=ConfParams.PORT)
#             self.connection = self.psycopg_pool.connection()
#             self.cursor = self.connection.cursor()
#         except Exception as e:
#             logger.error('数据库连接错误:%s' % e)
#             raise
#
#     # 取单条数据
#     def queryOne(self, sql):
#         self.cursor.execute(sql)
#         return self.cursor.fetchone()


# if __name__ == '__main__':
#     DB = DataBase(match='')
#     sql = "select * from table1"
#     result = DB.queryOne(sql)
#     print(result)


class PGDW:
    # 连接
    def __init__(self):
        try:
            self.connect,self.engine = get_engine(DBConfig.PGDWConfig.DB_ENGINE_STR)
        except Exception as e:
            logger.error('数据库连接错误:%s' % e)
            raise

    def get_single_one_extr(self, quyu, html_key):
        """根据区域与html_key 确定某个公告"""
        table_name = "src.t_gg_1_prt_{quyu}".format(quyu=quyu)
        sql02 = """SELECT * from  {table_name} t 
                                    where t.quyu = '{quyu}' and  t.html_key = {html_key}
                                  """.format(table_name=table_name, quyu=quyu, html_key=html_key)
        try:
            res = []
            db_re = self.connect.execute(sql02)
            if db_re:
                res = dict(db_re.fetchone())
            return res
        except:
            logger.error(traceback.format_exc())
            return []

    def get_samples(self, quyu, limit_num):
        """ """
        sql01 = """select *  from src.t_gg_1_prt_{quyu} t offset floor(random()*2)  LIMIT  {limit_num}
                        """.format(quyu=quyu, limit_num=limit_num)
        df_01 = pd.read_sql(sql=sql01, con=self.engine)
        return df_01

    def get_quyu_all_samples(self, quyu):
        """ """
        sql01 = """select *  from  src.t_gg_1_prt_{quyu} t  where  t.ggtype ~ '中标|招标|其他' """.format(quyu=quyu)
        df_01 = pd.read_sql(sql=sql01, con=self.engine)
        if df_01.shape[0]<1:
            return
        return df_01



# if __name__ == '__main__':
#     pg = PGDW()
#     res = pg.get_single_one_extr('beijing_haidian_ggzy','1296259')
#     print(res)
#     res02 = pg.get_samples('beijing_haidian_ggzy',129)
#     print(res02)
